package com.corpdata.app.customer.dao;

import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.type.JdbcType;

import com.corpdata.app.customer.entity.SellRecord;
import com.corpdata.core.base.BaseMapper;
import com.github.pagehelper.Page;

/**
 *  持久层接口
 * @author wu
 * @email 64088839@qq.com
 * @date 2018-07-18 15:06:14
 */
public interface SellRecordMapper extends BaseMapper<SellRecord>{

	/**
	 * 扩展查询 一对多关联参观记录
	 * 销售记录只要当前客户下最新的一条
	 * @param id
	 * @return
	 */
	@Select("select a.*,b.id b_id,b.Fk_projectid b_Fk_projectid,b.Fk_customer b_Fk_customer, " + 
			"b.Fk_Sell_record b_Fk_Sell_record,b.Visit_user b_Visit_user,b.Visit_date b_Visit_date, " + 
			"b.Visit_Remarks b_Visit_Remarks,b.created b_created, "+
			"c.id c_id,c.Fk_projectid c_Fk_projectid, d.project_name c_projectName,c.Fk_customer c_Fk_customer,c.Fk_Sell_record c_Fk_Sell_record,c.Fk_partition c_Fk_partition,e.Partition_name c_PartitionName, c.Admission_Desire c_Admission_Desire,c.Remark c_Remark,c.Created c_Created  " + 
			"from ( " + 
			"	select * from sell_record where Fk_customer=#{cid} ORDER BY created desc limit 1 " + 
			") a  left join visit_record b on a.id=b.Fk_Sell_record "+
			"left join recommend_record c on c.Fk_Sell_record=a.id "+
			"left join project_info d on c.Fk_projectid=d.id "+
			"left join project_partition e on c.Fk_partition=e.id "
			)
	@ResultMap("ExtendsResultMap")
	SellRecord selectExt(String cid);
	
	/**
	 * Fk_dic_Cause_loss 用这个字段绑定执行人..
	 * @param cId
	 * @return
	 */
	@Select("<script>select b.customer_Name fk_Customer,a.sell_info,a.Customer_state,c.user_name Fk_dic_Cause_loss,a.created "+
			"from sell_record a " +
			"inner join customer_info b on a.Fk_customer=b.id " + 
			"inner join org_user c on c.id=b.Executor "+
			"where 1=1 "+
			"<if test='cId != null'> and a.Fk_customer=#{cId}</if>"+
			"<if test='executor != null'> and b.executor=#{executor}</if>"+
			"<if test='customerState != null'> and a.Customer_state=#{customerState}</if>"+
			"<if test='sellDateSta != null and sellDateEnd != null'> and a.created&gt;=#{sellDateSta} and a.created&lt;=#{sellDateEnd}</if>"+
			" ORDER BY a.created desc </script>")
	@ResultMap("BaseResultMap")
	Page<SellRecord> selectBycIdAnduId(Map<String,Object> params);

	/**
	 * 根据客户 将所有数据修改为非使用态  on_use=0
	 * @param cId
	 * @return
	 */
	@Update("update sell_record set on_use=0 where Fk_customer=#{cId}")
	int updateBycId(String cId);

	@Select("<script>select " +
			"sum(case Customer_state when 0 then 1 else 0 end ) initial, " + 
			"sum(case Customer_state when 1 then 1 else 0 end ) win, " + 
			"sum(case Customer_state when 2 then 1 else 0 end ) lose " + 
			"from sell_record where on_use=1 "+ 
			"<if test='time.length()==7'>and DATE_FORMAT(created,'%Y-%m') =#{time} </if>"+ //2017-07
			"<if test='time.length()==6'>and DATE_FORMAT(created,'%Y%u') = #{time} </if>"+ //201730
			"</script>")
	@Results(value= {
			@Result(column="initial",property="initial",jdbcType=JdbcType.INTEGER,javaType=Integer.class),
			@Result(column="win",property="win",jdbcType=JdbcType.INTEGER,javaType=Integer.class),
			@Result(column="lose",property="lose",jdbcType=JdbcType.INTEGER,javaType=Integer.class)
	})
	Map<String, Integer> selectCountByDate(@Param("time")String time);

	@Delete("delete from sell_record where fk_customer=#{cusId}")
	int deleteByFkCustomer(String cusId);

}
